package com.dhgate.spu.analysis.util;


import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;



/**
 * 账单报表
 * 
 * @author yangqin@dhgate.com
 * 
 */
public class ExcelUtil {
    private static final Log log = LogFactory.getLog(ExcelUtil.class);

    private POIFSFileSystem fs;
    private HSSFWorkbook wb;
    private HSSFSheet sheet;
    private HSSFRow row;
    /**
     * 不分页的导出功能
     * 
     * @param heads
     * @param bodyList
     * @return
     */
    public static InputStream export(Object[] heads, List<Object[]> bodyList) {

        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("sheet1");
        HSSFRow row0 = sheet.createRow(0);
        if (heads != null) {
            for (int i = 0; i < heads.length; i++) {
                HSSFCell cellT1 = row0.createCell(i);
                cellT1.setCellValue(heads[i].toString());
            }
        }

        if (bodyList != null) {
            for (int j = 0; j < bodyList.size(); j++) {
                Object[] bodys = bodyList.get(j);
                if (bodys != null) {
                    HSSFRow row = sheet.createRow(j + 1);
                    for (int i = 0; i < bodys.length; i++) {
                        HSSFCell cell = row.createCell(i);
                        setCellValue(cell, bodys[i]);
                    }
                }
            }
        }

        ByteArrayOutputStream out = new ByteArrayOutputStream();
        try {
            workbook.write(out);
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }

        return new ByteArrayInputStream(out.toByteArray());

    }

    /**
     * 创建一个workbook
     * 
     * @return
     */
    public static HSSFWorkbook creatWorkbook() {
        HSSFWorkbook workbook = new HSSFWorkbook();
        return workbook;
    }

    /**
     * 创建一页sheet
     * 
     * @param workbook
     * @return
     */
    public static HSSFSheet creatSheet(HSSFWorkbook workbook) {
        // sheet 名字统一设置
        HSSFSheet sheet = workbook.createSheet("sheet1");
        return sheet;
    }

    /**
     * 创建每一页
     * 
     * @param sheet
     * @param bodyList
     * @param startIndx
     *            第一页第一行的开始row
     * @param pageIndex
     * @param pageSize
     */
    public static void createPageRows(HSSFSheet sheet, List<Object[]> bodyList, int startIndx, int pageIndex, int pageSize) {
        int rowindex = (pageIndex - 1) * pageSize;
        if (bodyList != null) {

            for (int j = 0; j < bodyList.size(); j++) {
                Object[] bodys = bodyList.get(j);

                if (bodys != null) {
                    int currentRow = j + startIndx + rowindex;
                    HSSFRow row = sheet.createRow(currentRow);

                    for (int i = 0; i < bodys.length; i++) {
                        HSSFCell cell = row.createCell(i);
                        setCellValue(cell, bodys[i]);
                    }

                }

            }
        }
    }

    /**
     * 创建标题
     * 
     * @param sheet
     * @param heads
     */
    public static void createHead(HSSFSheet sheet, Object[] heads) {
        HSSFRow row0 = sheet.createRow(0);
        if (heads != null) {
            for (int i = 0; i < heads.length; i++) {
                HSSFCell cell = row0.createCell(i);
                cell.setCellValue(heads[i].toString());
            }
        }
    }

    /**
     * workbook转换成inputstream
     * 
     * @param workbook
     * @return
     */
    public static InputStream getInputStream(HSSFWorkbook workbook) {
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        try {
            workbook.write(out);
            out.flush();
        } catch (IOException e) {
            log.error("", e);
        } finally {
            try {
                out.close();
            } catch (IOException e) {
                log.error("", e);
            }
        }

        return new ByteArrayInputStream(out.toByteArray());
    }

    /**
     * 设置cell值
     * 
     * @param cell
     * @param obj
     */
    private static void setCellValue(HSSFCell cell, Object obj) {
        if (obj == null) {
            cell.setCellValue("");
        } else {
            try {
                cell.setCellValue(Double.valueOf(String.valueOf(obj)));
            } catch (NumberFormatException e) {
                cell.setCellValue((String) obj);
            }
        }
    }

    /**
     * 获取总行数
     * @return
     */
    public Integer getSheetRowCount(InputStream is){
        try {
            fs = new POIFSFileSystem(is);
            wb = new HSSFWorkbook(fs);
        } catch (IOException e) {
            e.printStackTrace();
        }
        sheet = wb.getSheetAt(0);
        // 得到总行数
        int rowNum = sheet.getLastRowNum();
        return rowNum;
    }
    
	 /**
     * 读取Excel数据内容
     * @param InputStream
     * @param endRow  为-1时表示剩余的全部,countRow=100表示从startRow数一百行
     * @return Map 包含单元格数据内容的Map对象
     */
    public void readExcelContent(InputStream is,int startRow,int countRow) {

        try {
            fs = new POIFSFileSystem(is);
            wb = new HSSFWorkbook(fs);
        } catch (IOException e) {
            e.printStackTrace();
        }
        sheet = wb.getSheetAt(0);
        // 得到总行数
    	if(startRow < 0){
    		startRow = 0;
    	}
        int rowNum;
        if(countRow == -1){
        	rowNum = sheet.getLastRowNum();
        }else{
        	rowNum = (startRow + countRow) > sheet.getLastRowNum()?sheet.getLastRowNum():(startRow+countRow);
        }
        row = sheet.getRow(startRow);
        int colNum = row.getPhysicalNumberOfCells();
        // 正文内容应该从第二行开始,第一行为表头的标题
        for (int i = startRow; i <= rowNum; i++) {
            row = sheet.getRow(i);
            int j = 0;
            while (j < colNum) {
                // 每个单元格的数据内容用"-"分割开，以后需要时用String类的replace()方法还原数据
                // 也可以将每个单元格的数据设置到一个javabean的属性中，此时需要新建一个javabean
            	System.out.print(row.getCell(j)+"  ");
//              str += getCellFormatValue(row.getCell((short) j)).trim() + "    ";
                j++;
            }
            System.out.println();
        }
    }
    
	    
    
}
